/* $RCSFile: AnnouncementsDB.cs $
 * Apache license v2 2011 by Christian Surieux
 * $Source: ?/AddonNice/PageModules/Announcements/AnnouncementsDB.cs $
 * $log$
 * Revision 19 2010/08/09 09:43:45  christian.surieux
 *   Added Log to cs sources files
 * Revision 18 2010/08/06 19:56:24  christian.surieux
 *   Added Log to All cs files
 */

using System;
using System.Diagnostics;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;

using AddonNice.Configuration;
using AddonNice.Diagnostics;

namespace AddonNice.Settings.PageModules.Announcements
{
	/// <summary>
	/// Class that encapsulates all data logic necessary to add/query/delete
	/// announcements within the Portal database.
	/// </summary>
    public class AnnouncementsDB : DBDataStore
    {
		/// <summary>
		/// The GetAnnouncements method returns a DataTable containing all of the
		/// announcements for a specific portal module from the Announcements
		/// database table.
		/// </summary>
        public DataTable GetAnnouncements(int moduleID) 
        {
            SqlDataAdapter myAdapt			    =   new SqlDataAdapter("dbo.adn_GetAnnouncements", myConnection);
            myAdapt.SelectCommand.CommandType   =   CommandType.StoredProcedure;

            AddModuleIDParameters(myAdapt.SelectCommand,moduleID);

			DataTable myDataSet					=   new DataTable();
			try
			{
				myAdapt.Fill(myDataSet);
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]AnnouncementsDB GetAnnouncements ex: {0}",exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
			return CheckDataTableForDates(myDataSet);
        }

		/// <summary>
		/// The GetSingleAnnouncement method returns a SqlDataReader containing details
		/// about a specific announcement from the Announcements database table.
		/// </summary>
        public SqlDataReader GetSingleAnnouncement(int itemID) 
        {
            SqlCommand myCommand			= new SqlCommand("dbo.adn_GetSingleAnnouncement", myConnection);
            myCommand.CommandType			= CommandType.StoredProcedure;

            AddItemIDParameters(myCommand,itemID);


			SqlDataReader result			= null;
			try
			{
				myConnection.Open();
				result						= myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]AnnouncementsDB GetSingleAnnouncement itemID : {0} \nex : {1}",itemID,exp),DBTraceSwitch.Sw.Info);
				if ( result != null )
					result.Close();
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
				throw;
			}
			return result;
        }

		/// <summary>
		/// The DeleteAnnouncement method deletes the specified announcement from
		/// the Announcements database table.
		/// </summary>
        public void DeleteAnnouncement(int itemID) 
        {
            SqlCommand myCommand			= new SqlCommand("dbo.adn_DeleteAnnouncement", myConnection);
            myCommand.CommandType			= CommandType.StoredProcedure;

            AddItemIDParameters(myCommand,itemID);

			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]AnnouncementsDB DeleteAnnouncement  itemID:{0} ex : {1}",itemID,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}

        /// <summary>
        /// The AddAnnouncement method adds a new announcement to the
        /// Announcements database table, and returns the ItemID value as a result.
        /// </summary>
        public int AddAnnouncement(int moduleID,string userName,string title, DateTime expireDate,string description,string moreLink) 
        {
            if (userName.Length < 1) 
            {
                userName                        =   StStr.unknown;
            }

            SqlCommand myCommand                =   new SqlCommand("dbo.adn_AddAnnouncement", myConnection);
            myCommand.CommandType               =   CommandType.StoredProcedure;

            SqlParameter parameterItemID		=	new SqlParameter(StStr.ATItemID, SqlDbType.Int, 4);
            parameterItemID.Direction           =   ParameterDirection.Output;
            myCommand.Parameters.Add(parameterItemID);

            AddModuleIDParameters(myCommand,moduleID);

            SqlParameter parameterUserName      =   new SqlParameter("@UserName", SqlDbType.NVarChar, 100);
            parameterUserName.Value             =   userName;
            myCommand.Parameters.Add(parameterUserName);

            SqlParameter parameterTitle         =   new SqlParameter("@Title", SqlDbType.NVarChar, 150);
            parameterTitle.Value                =   title;
            myCommand.Parameters.Add(parameterTitle);

            SqlParameter parameterMoreLink      =   new SqlParameter("@MoreLink", SqlDbType.NVarChar, 150);
            parameterMoreLink.Value             =   moreLink;
            myCommand.Parameters.Add(parameterMoreLink);

            SqlParameter parameterExpireDate    =   new SqlParameter("@ExpireDate", SqlDbType.DateTime, 8);
            parameterExpireDate.Value           =   expireDate;
            myCommand.Parameters.Add(parameterExpireDate);

            SqlParameter parameterDescription   =   new SqlParameter("@Description", SqlDbType.NVarChar, description.Length);
            parameterDescription.Value          =   description;
            myCommand.Parameters.Add(parameterDescription);

			int ret							    =	-1;
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				ret			                    =	(int)parameterItemID.Value;
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]AnnouncementsDB AddAnnouncement  moduleID:{0} userName:{1} \nex : {2}",moduleID,userName, exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
			return ret;
        }

        //*********************************************************************
        //
        // UpdateAnnouncement Method
        //
        // The UpdateAnnouncement method updates the specified announcement within
        // the Announcements database table.
        //
        // Other relevant sources:
        //     + <a href="UpdateAnnouncement.htm" style="color:green">UpdateAnnouncement Stored Procedure</a>
        //
        //*********************************************************************

        public void UpdateAnnouncement(int moduleID, int itemID,string userName,string title, DateTime expireDate,string description,string moreLink) 
        {

            if (userName.Length < 1) userName   =   StStr.unknown;

            SqlCommand myCommand                =   new SqlCommand("dbo.adn_UpdateAnnouncement", myConnection);
            myCommand.CommandType               =   CommandType.StoredProcedure;

            AddItemIDParameters(myCommand,itemID);

            SqlParameter parameterUserName      =   new SqlParameter("@UserName", SqlDbType.NVarChar, 100);
            parameterUserName.Value             =   userName;
            myCommand.Parameters.Add(parameterUserName);

            SqlParameter parameterTitle         =   new SqlParameter("@Title", SqlDbType.NVarChar, 150);
            parameterTitle.Value                =   title;
            myCommand.Parameters.Add(parameterTitle);

            SqlParameter parameterMoreLink      =   new SqlParameter("@MoreLink", SqlDbType.NVarChar, 150);
            parameterMoreLink.Value             =   moreLink;
            myCommand.Parameters.Add(parameterMoreLink);

            SqlParameter parameterExpireDate    =   new SqlParameter("@ExpireDate", SqlDbType.DateTime, 8);
            parameterExpireDate.Value           =   expireDate;
            myCommand.Parameters.Add(parameterExpireDate);

            SqlParameter parameterDescription   =   new SqlParameter("@Description", SqlDbType.NVarChar, description.Length);
            parameterDescription.Value          =   description;
            myCommand.Parameters.Add(parameterDescription);

			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]AnnouncementsDB UpdateAnnouncement  itemID:{0} userName:{1} \nex : {2}",itemID,userName, exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}

		public DataTable GetAnnouncementsGlobalized(int moduleID) 
		{
			SqlDataAdapter myAdapt			    =   new SqlDataAdapter("dbo.adn_GetAnnouncementsGlobalized", myConnection);
			myAdapt.SelectCommand.CommandType   =   CommandType.StoredProcedure;

			AddGlobalizeParameters(myAdapt.SelectCommand);

            AddModuleIDParameters(myAdapt.SelectCommand,moduleID);

			DataTable myDataSet					=   new DataTable();
			try
			{
				myAdapt.Fill(myDataSet);
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]AnnouncementsDB GetAnnouncementsGlobalized ex: {0}",exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
			return CheckDataTableForDates(myDataSet);
		}

        /// <summary>
		/// The GetSingleAnnouncement method returns a SqlDataReader containing details
		/// about a specific announcement from the Announcements database table.
		/// </summary>
		public SqlDataReader GetSingleAnnouncementGlobalized(int itemID) 
		{
			SqlCommand myCommand				=   new SqlCommand("dbo.adn_GetSingleAnnouncementGlobalized", myConnection);
			myCommand.CommandType				=   CommandType.StoredProcedure;

			AddGlobalizeParameters(myCommand);

            AddItemIDParameters(myCommand,itemID);


			SqlDataReader result			    =   null;
			try
			{
				myConnection.Open();
				result						    =   myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]AnnouncementsDB GetSingleAnnouncementGlobalized itemID : {0} \nex : {1}",itemID,exp),DBTraceSwitch.Sw.Info);
				if ( result != null )
					result.Close();
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
				throw;
			}
			return result;
		}
	
		public void DeleteAnnouncementGlobalized(int itemID) 
		{
			SqlCommand myCommand			    =   new SqlCommand("dbo.adn_DeleteAnnouncementGlobalized", myConnection);
			myCommand.CommandType			    =   CommandType.StoredProcedure;

            AddItemIDParameters(myCommand,itemID);


			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]AnnouncementzsDB DeleteAnnouncementGlobalized  itemID:{0}, ex : {1}",itemID, exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}

		public void UpdateAnnouncementGlobalized(int moduleID, int itemID,string userName,string title, DateTime expireDate,string description,string moreLink) 
		{

			if (userName.Length < 1) userName   =   StStr.unknown;

			SqlCommand myCommand				=   new SqlCommand("dbo.adn_UpdateAnnouncementGlobalized", myConnection);
			myCommand.CommandType				=   CommandType.StoredProcedure;

			AddGlobalizeParameters(myCommand);

            AddItemIDParameters(myCommand,itemID);

            AddModuleIDParameters(myCommand,moduleID);

			SqlParameter parameterUserName      =   new SqlParameter("@UserName", SqlDbType.NVarChar, 100);
			parameterUserName.Value             =   userName;
			myCommand.Parameters.Add(parameterUserName);

			SqlParameter parameterTitle         =   new SqlParameter("@Title", SqlDbType.NVarChar, 150);
			parameterTitle.Value                =   title;
			myCommand.Parameters.Add(parameterTitle);

			SqlParameter parameterMoreLink      =   new SqlParameter("@MoreLink", SqlDbType.NVarChar, 150);
			parameterMoreLink.Value             =   moreLink;
			myCommand.Parameters.Add(parameterMoreLink);

			SqlParameter parameterExpireDate    =   new SqlParameter("@ExpireDate", SqlDbType.DateTime, 8);
			parameterExpireDate.Value           =   expireDate;
			myCommand.Parameters.Add(parameterExpireDate);

			SqlParameter parameterDescription   =   new SqlParameter("@Description", SqlDbType.NVarChar, description.Length);
			parameterDescription.Value          =   description;
			myCommand.Parameters.Add(parameterDescription);

			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if (DBTraceSwitch.Sw.TraceError)
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]AnnouncementzsDB UpdateAnnouncementGlobalized  itemID:{0} userName:{1} \nex : {2}",itemID,userName, exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}
	
	}
}
